**************************************************************
*HURRICANES AND GAS GOUGING - DATA CLEAN
**************************************************************

****************************
*Basic data cleaning
rename day dow 
gen week=week(date) 
rename retail_unleaded retail 
gen wholesale = whole_rack1/100 
	replace wholesale=whole_rack2/100 if missing(wholesale) 
gen weekofperiod=((year-2004)*52)+week 
gen yw=yw(year,week)
	format yw %tw	
gen ym=ym(year,month)	
	format ym %tm	
	
*Merge in bulk gas prices
merge m:1 date using $data_davis\WORKING_SPOTPRICE
drop if _merge==2
drop _merge
rename spotprice bulk_original
rename spotprice_interp bulk
order FuellocationID date name retail wholesale bulk

*Merge in landfall data. 
merge m:1 FuellocationID date using $data_davis\WORKING_LANDFALLS, gen(lmerge)
drop _merge

*Merge in nearest highway
merge m:1 FuellocationID using $data_davis\WORKING_NEAREST_HIGHWAY
drop hw_nearest_feature _merge

*Merge in outage data. 
merge 1:1 FuellocationID date using $data_davis\WORKING_OUTAGE
drop _merge 
rename hr_name outage_storm

*Merge in FEMA declarations data.
merge m:1 county_FIPS date using $data_davis\WORKING_FEMA_DECLARATION
drop if _merge==2
drop _merge
replace fema_disaster=0 if missing(fema_disaster)
replace fema_indiv_hh_disaster=0 if missing(fema_indiv_hh_disaster)

*Merge in traffic data
merge 1:1 FuellocationID date using $data_davis\WORKING_TRAFFIC  
drop _merge

*Creating temperature controls
gen temp=tmean_near1
gen temp2=tmean_near1^2

*Destring county fips code
destring county_FIPS, replace

*Renaming station id
rename FuellocationID station_id

*Coastal PFZs  
gen coast_pfz=0
	egen temp_var=rowmax(CTSN CTST CHRN CHRT) 
	bys station_id: egen ever=max(temp_var) 
	drop temp_var
	replace coast_pfz=1 if !missing(ever)
	drop ever
	
**************************************
*Updating storm names 
* Notes: - Stats on hurricane warnings, landfalls, and stations
*		    Storm (State)   1st HW			1st Landfall 	Stations Hit (Reporting)	Wind (High)
*			Bonnie/Charlie	11aug2004       12aug2004		887 (548)					40/130 (55/130)
*			Frances		    02sep2004       06sep2004		184 (113)					50 (125)
*			Ivan    		14sep2004 		16sep2004		153 (7)						105 (145)
*			Jeanne		    24sep2004		26sep2004		1007 (246)					105 (105)
*			Arlene			10jun2005		11jun2005	   	143 (90)					50 (60)
*			Dennis   	    07jul2005		10jul2005  		176 (5)						105 (130)
*			Katrina	(FL)	24aug2005 		25aug2005  		1038 (966)					70 (150)
*			Katrina	(LA)	27aug2005    	29aug2005		216 (5)						110  (150)
*			Rita    		18sep2005		24sep2005 		99 (4)						100 (155)
*			Wilma		    22oct2005		24oct2005		988 (146)					105 (160)
*			Alberto		    12jun2006       13jun2006		324 (306)					40 (60)
*			Humberto	    13sep2007		13sep2007 		67 (64)						80 (80)
*			Gustav		    31aug2008		01sep2008 		567 (68)					90 (135)
*			Ike		        11sep2008		13sep2008 		8 (2)						95 (125)
*Storm name
replace storm_name="BONCHAR" if storm_name=="BONNIE"|storm_name=="CHARLEY"
replace storm_name="KATRINA_LA" if storm_name=="KATRINA"

*Updating warning/watch variables	
replace CTST=0 if missing(CTST)
replace CHRT=0 if missing(CHRT)
replace CTSN=0 if missing(CTSN)
replace CHRN=0 if missing(CHRN)

*Tropical storm watch
replace CTST_name="BONCHAR" if CTST_name=="BONNIE"|CTST_name=="CHARLEY"
replace CTST_name="KATRINA_LA" if CTST_name=="KATRINA" & state_name=="Florida"
replace CTST_name="KATRINA_FL" if CTST_name=="KATRINA" & state_name=="Louisiana"

*Hurricane watch
replace CHRT_name="BONCHAR" if CHRT_name=="BONNIE"|CHRT_name=="CHARLEY"
replace CHRT_name="KATRINA_LA" if CHRT_name=="KATRINA" & state_name=="Florida"
replace CHRT_name="KATRINA_FL" if CHRT_name=="KATRINA" & state_name=="Louisiana"

*Tropical storm warning
replace CTSN_name="BONCHAR" if CTSN_name=="BONNIE"|CTSN_name=="CHARLEY"
replace CTSN_name="KATRINA_LA" if CTSN_name=="KATRINA" & state_name=="Florida"
replace CTSN_name="KATRINA_FL" if CTSN_name=="KATRINA" & state_name=="Louisiana"

*Hurricane warning
replace CHRN_name="BONCHAR" if CHRN_name=="BONNIE"|CHRN_name=="CHARLEY"
replace CHRN_name="KATRINA_LA" if CHRN_name=="KATRINA" & state_name=="Florida"
replace CHRN_name="KATRINA_FL" if CHRN_name=="KATRINA" & state_name=="Louisiana"

****
*Creating time-invariant station indicators by storm
*
*Stations hit by landfall
local names "ALBERTO ARLENE BONCHAR DENNIS FRANCES GUSTAV HUMBERTO IKE IVAN JEANNE KATRINA_LA KATRINA_FL RITA WILMA"
foreach h of local names {
	gen `h'_landfall=1 if storm_name=="`h'"
	gsort station_id -`h'_landfall
	by station_id: carryforward `h'_landfall, replace
	replace `h'_landfall=0 if missing(`h'_landfall)
}
*
*Stations under watch/warning but not hit by landfall
local names "ALBERTO ARLENE BONCHAR DENNIS FRANCES GUSTAV HUMBERTO IKE IVAN JEANNE KATRINA_LA KATRINA_FL RITA WILMA"
foreach h of local names {
	gen `h'_ww=1 if (CTST_name=="`h'"|CHRT_name=="`h'"|CTSN_name=="`h'"|CHRN_name=="`h'")
		replace `h'_ww=0 if `h'_landfall==1
	gsort station_id -`h'_ww
	by station_id: carryforward `h'_ww, replace
	replace `h'_ww=0 if missing(`h'_ww)
}
*
*Inland stations not hit by landfall
local names "ALBERTO ARLENE BONCHAR DENNIS FRANCES GUSTAV HUMBERTO IKE IVAN JEANNE KATRINA_LA KATRINA_FL RITA WILMA"
foreach h of local names {
	gen `h'_inland=1 if `h'_landfall==0 & `h'_ww==0 & coast_pfz==0 
		replace `h'_inland=0 if missing(`h'_inland)
}
*
*
*Stations not affected (inland or coastal)
local names "ALBERTO ARLENE BONCHAR DENNIS FRANCES GUSTAV HUMBERTO IKE IVAN JEANNE KATRINA_LA KATRINA_FL RITA WILMA"
foreach h of local names {
	gen `h'_noimpact=1 if `h'_landfall==0 & `h'_ww==0  
		replace `h'_noimpact=0 if missing(`h'_inland)
}
*
*
****************
*CREATING HURRICANE INDICATORS FOR DIF-IN-DIF
*Hurricane Indicators  	
gen hur=0 
	replace hur=1 if BONCHAR_landfall==1 & date==`=td(12aug2004)'
	replace hur=1 if FRANCES_landfall==1 & date==`=td(06sep2004)'
	replace hur=1 if IVAN_landfall==1 & date==`=td(16sep2004)'
	replace hur=1 if JEANNE_landfall==1 & date==`=td(26sep2004)'
	replace hur=1 if ARLENE_landfall==1 & date==`=td(11jun2005)'
	replace hur=1 if DENNIS_landfall==1 & date==`=td(10jul2005)'
	replace hur=1 if KATRINA_FL_landfall==1 & date==`=td(25aug2005)'
	replace hur=1 if KATRINA_LA_landfall==1 & date==`=td(29aug2005)'
	replace hur=1 if RITA_landfall==1 & date==`=td(24sep2005)'
	replace hur=1 if WILMA_landfall==1 & date==`=td(24oct2005)'
	replace hur=1 if ALBERTO_landfall==1 & date==`=td(13jun2006)'
	replace hur=1 if HUMBERTO_landfall==1 & date==`=td(13sep2007)'
	replace hur=1 if GUSTAV_landfall==1 & date==`=td(01sep2008)'
	replace hur=1 if IKE_landfall==1 & date==`=td(13sep2008)'
gen post_hur=0
gen pre_hur=0

*Filling in pre-hurricane indicators
gsort station_id date
xtset station_id date
gen hur_temp=0 //First day of hurricane
	replace hur_temp=1 if hur==1 & l.hur==0
forvalues t = 1/3 {
	gen y=f`t'.year
	replace hur=1 if f`t'.hur_temp==1 & y==year
	drop y
}
*
forvalues t = 4/14 {
	gen y=f`t'.year
	replace pre_hur=1 if f`t'.hur_temp==1 & y==year
	drop y
}
*
drop hur_temp

*Filling in post-hurricane indicators
gen hur_temp=0 //Last day of hurricane
	replace hur_temp=1 if hur==1 & f.hur==0	
forvalues t = 1/3 {
	gen y=l`t'.year
	replace hur=1 if l`t'.hur_temp==1 & y==year
	drop y
}
*
forvalues t = 4/14 {
	gen y=l`t'.year
	replace post_hur=1 if l`t'.hur_temp==1 & y==year
	drop y
}
*
drop hur_temp
*
*****************************************
*HURRICANE INDICATORS DIF-IN-DIF HETEROGENEITY - LOCATION
gsort station_id date
gen hur_landfall=0 
	replace hur_landfall=1 if BONCHAR_landfall==1 & date==`=td(12aug2004)'
	replace hur_landfall=1 if FRANCES_landfall==1 & date==`=td(06sep2004)'
	replace hur_landfall=1 if IVAN_landfall==1 & date==`=td(16sep2004)'
	replace hur_landfall=1 if JEANNE_landfall==1 & date==`=td(26sep2004)'
	replace hur_landfall=1 if ARLENE_landfall==1 & date==`=td(11jun2005)'
	replace hur_landfall=1 if DENNIS_landfall==1 & date==`=td(10jul2005)'
	replace hur_landfall=1 if KATRINA_FL_landfall==1 & date==`=td(25aug2005)'
	replace hur_landfall=1 if KATRINA_LA_landfall==1 & date==`=td(29aug2005)'
	replace hur_landfall=1 if RITA_landfall==1 & date==`=td(24sep2005)'
	replace hur_landfall=1 if WILMA_landfall==1 & date==`=td(24oct2005)'
	replace hur_landfall=1 if ALBERTO_landfall==1 & date==`=td(13jun2006)'
	replace hur_landfall=1 if HUMBERTO_landfall==1 & date==`=td(13sep2007)'
	replace hur_landfall=1 if GUSTAV_landfall==1 & date==`=td(01sep2008)'
	replace hur_landfall=1 if IKE_landfall==1 & date==`=td(13sep2008)'
gen post_hur_landfall=0
gen pre_hur_landfall=0
	
*Hurricane watch-warning day 0
gen hur_ww=0 
	replace hur_ww=1 if BONCHAR_ww==1 & date==`=td(12aug2004)'
	replace hur_ww=1 if FRANCES_ww==1 & date==`=td(06sep2004)'
	replace hur_ww=1 if IKE_ww==1 & date==`=td(16sep2004)'
	replace hur_ww=1 if JEANNE_ww==1 & date==`=td(26sep2004)'
	replace hur_ww=1 if ARLENE_ww==1 & date==`=td(11jun2005)'
	replace hur_ww=1 if DENNIS_ww==1 & date==`=td(10jul2005)'
	replace hur_ww=1 if KATRINA_FL_ww==1 & date==`=td(25aug2005)'
	replace hur_ww=1 if KATRINA_LA_ww==1 & date==`=td(29aug2005)'
	replace hur_ww=1 if RITA_ww==1 & date==`=td(24sep2005)'
	replace hur_ww=1 if WILMA_ww==1 & date==`=td(24oct2005)'
	replace hur_ww=1 if ALBERTO_ww==1 & date==`=td(13jun2006)'
	replace hur_ww=1 if HUMBERTO_ww==1 & date==`=td(13sep2007)'
	replace hur_ww=1 if GUSTAV_ww==1 & date==`=td(01sep2008)'
	replace hur_ww=1 if IVAN_ww==1 & date==`=td(13sep2008)'	
gen post_hur_ww=0
gen pre_hur_ww=0

*Hurricane inland day 0
gen hur_inland=0 
	replace hur_inland=1 if BONCHAR_inland==1 & date==`=td(12aug2004)'
	replace hur_inland=1 if FRANCES_inland==1 & date==`=td(06sep2004)'
	replace hur_inland=1 if IVAN_inland==1 & date==`=td(16sep2004)'
	replace hur_inland=1 if JEANNE_inland==1 & date==`=td(26sep2004)'
	replace hur_inland=1 if ARLENE_inland==1 & date==`=td(11jun2005)'
	replace hur_inland=1 if DENNIS_inland==1 & date==`=td(10jul2005)'
	replace hur_inland=1 if KATRINA_FL_inland==1 & date==`=td(25aug2005)'
	replace hur_inland=1 if KATRINA_LA_inland==1 & date==`=td(29aug2005)'
	replace hur_inland=1 if RITA_inland==1 & date==`=td(24sep2005)'
	replace hur_inland=1 if WILMA_inland==1 & date==`=td(24oct2005)'
	replace hur_inland=1 if ALBERTO_inland==1 & date==`=td(13jun2006)'
	replace hur_inland=1 if HUMBERTO_inland==1 & date==`=td(13sep2007)'
	replace hur_inland=1 if GUSTAV_inland==1 & date==`=td(01sep2008)'
	replace hur_inland=1 if IKE_inland==1 & date==`=td(13sep2008)'
gen post_hur_inland=0
gen pre_hur_inland=0

*Hurricane no impact day 0
gen hur_noimpact=0 
	replace hur_noimpact=1 if BONCHAR_noimpact==1 & date==`=td(12aug2004)'
	replace hur_noimpact=1 if FRANCES_noimpact==1 & date==`=td(06sep2004)'
	replace hur_noimpact=1 if IVAN_noimpact==1 & date==`=td(16sep2004)'
	replace hur_noimpact=1 if JEANNE_noimpact==1 & date==`=td(26sep2004)'
	replace hur_noimpact=1 if ARLENE_noimpact==1 & date==`=td(11jun2005)'
	replace hur_noimpact=1 if DENNIS_noimpact==1 & date==`=td(10jul2005)'
	replace hur_noimpact=1 if KATRINA_FL_noimpact==1 & date==`=td(25aug2005)'
	replace hur_noimpact=1 if KATRINA_LA_noimpact==1 & date==`=td(29aug2005)'
	replace hur_noimpact=1 if RITA_noimpact==1 & date==`=td(24sep2005)'
	replace hur_noimpact=1 if WILMA_noimpact==1 & date==`=td(24oct2005)'
	replace hur_noimpact=1 if ALBERTO_noimpact==1 & date==`=td(13jun2006)'
	replace hur_noimpact=1 if HUMBERTO_noimpact==1 & date==`=td(13sep2007)'
	replace hur_noimpact=1 if GUSTAV_noimpact==1 & date==`=td(01sep2008)'
	replace hur_noimpact=1 if IKE_noimpact==1 & date==`=td(13sep2008)'
gen post_hur_noimpact=0
gen pre_hur_noimpact=0

*Filling in pre-hurricane indicators
gen hur_temp_landfall=0 //First day of hurricane - Landfall Areas
	replace hur_temp_landfall=1 if hur_landfall==1 & l.hur_landfall==0
gen hur_temp_ww=0 //First day of hurricane - Watch/Warning Areas
	replace hur_temp_ww=1 if hur_ww==1 & l.hur_ww==0
gen hur_temp_inland=0 //First day of hurricane - Inland Areas 
	replace hur_temp_inland=1 if hur_inland==1 & l.hur_inland==0	
gen hur_temp_noimpact=0 //First day of hurricane - Inland Areas 
	replace hur_temp_noimpact=1 if hur_noimpact==1 & l.hur_noimpact==0	
*
forvalues t = 1/3 {
	gen y=f`t'.year
	replace hur_landfall=1 if f`t'.hur_temp_landfall==1 & y==year
	replace hur_ww=1 if f`t'.hur_temp_ww==1 & y==year
	replace hur_inland=1 if f`t'.hur_temp_inland==1 & y==year
	replace hur_noimpact=1 if f`t'.hur_temp_noimpact==1 & y==year	
	drop y
}
*
forvalues t = 4/14 {
	gen y=f`t'.year
	replace pre_hur_landfall=1 if f`t'.hur_temp_landfall==1 & y==year
	replace pre_hur_ww=1 if f`t'.hur_temp_ww==1 & y==year
	replace pre_hur_inland=1 if f`t'.hur_temp_inland==1 & y==year
	replace pre_hur_noimpact=1 if f`t'.hur_temp_noimpact==1 & y==year	
	drop y
}
*
drop hur_temp_landfall hur_temp_ww hur_temp_inland hur_temp_noimpact

*Filling in post-hurricane indicators
gen hur_temp_landfall=0 //First day of hurricane - Landfall Areas
	replace hur_temp_landfall=1 if hur_landfall==1 & f.hur_landfall==0
gen hur_temp_ww=0 //First day of hurricane - Watch/Warning Areas
	replace hur_temp_ww=1 if hur_ww==1 & f.hur_ww==0
gen hur_temp_inland=0 //First day of hurricane - Inland Areas
	replace hur_temp_inland=1 if hur_inland==1 & f.hur_inland==0	
gen hur_temp_noimpact=0 //First day of hurricane - Inland Areas
	replace hur_temp_noimpact=1 if hur_noimpact==1 & f.hur_noimpact==0	
*
forvalues t = 1/3 {
	gen y=l`t'.year
	replace hur_landfall=1 if l`t'.hur_temp_landfall==1 & y==year
	replace hur_ww=1 if l`t'.hur_temp_ww==1 & y==year
	replace hur_inland=1 if l`t'.hur_temp_inland==1 & y==year	
	replace hur_noimpact=1 if l`t'.hur_temp_noimpact==1 & y==year		
	drop y
}
*
forvalues t = 4/14 {
	gen y=l`t'.year
	replace post_hur_landfall=1 if l`t'.hur_temp_landfall==1 & y==year
	replace post_hur_ww=1 if l`t'.hur_temp_ww==1 & y==year
	replace post_hur_inland=1 if l`t'.hur_temp_inland==1 & y==year	
	replace post_hur_noimpact=1 if l`t'.hur_temp_noimpact==1 & y==year		
	drop y
}
*
drop hur_temp_landfall hur_temp_ww hur_temp_inland hur_temp_noimpact
*
*
*****************************************
*HURRICANE INDICATORS DIF-IN-DIF HETEROGENEITY - STATION TYPE
gen unbranded=0
	replace unbranded=1 if brand_maj==0 & ret_maj==0
gen hur_brand=hur*brand_maj
gen hur_ret=hur*ret_maj
gen hur_unbrand=hur*unbranded
gen pre_hur_brand=pre_hur*brand_maj
gen pre_hur_ret=pre_hur*ret_maj
gen pre_hur_unbrand=pre_hur*unbranded
gen post_hur_brand=post_hur*brand_maj
gen post_hur_ret=post_hur*ret_maj
gen post_hur_unbrand=post_hur*unbranded
*
*
*****************************************
*HURRICANE INDICATORS DIF-IN-DIF HETEROGENEITY - MARKET CONCENTRATION
*   
*No Competitors
gen comp_lo=0
	replace comp_lo=1 if within_5km_df==0
*1-2 Competitors
gen comp_mid=0
	replace comp_mid=1 if within_5km_df>=1 & within_5km_df<=2
*>3 Competitors
gen comp_hi=0
	replace comp_hi=1 if within_5km_df>2
	
gen hur_comp_hi=hur*comp_hi
gen hur_comp_mid=hur*comp_mid
gen hur_comp_lo=hur*comp_lo
gen pre_hur_comp_hi=pre_hur*comp_hi
gen pre_hur_comp_mid=pre_hur*comp_mid
gen pre_hur_comp_lo=pre_hur*comp_lo
gen post_hur_comp_hi=post_hur*comp_hi
gen post_hur_comp_mid=post_hur*comp_mid
gen post_hur_comp_lo=post_hur*comp_lo

*****************************************
*HURRICANE INDICATORS DIF-IN-DIF HETEROGENEITY - DEMOGRAPHICS

*Education variables
gen high_ed= edu_gradover+edu_bachelor+edu_associate+edu_college_nodeg+edu_high
gen ed_low=0
	replace ed_low=1 if high_ed<69
gen ed_mid=0
	replace ed_mid=1 if high_ed>=69 & high_ed<86.2
gen ed_high=0
	replace ed_high=1 if high_ed>=86.2

*Population variables
gen pop_low=0
	replace pop_low=1 if pop<3784
gen pop_mid=0
	replace pop_mid=1 if pop>=3784 & pop<7015
gen pop_high=0
	replace pop_high=1 if pop>=7015

*Poverty variables
gen pov_low=0
	replace pov_low=1 if poverty<5.4
gen pov_mid=0
	replace pov_mid=1 if poverty>=5.4 & poverty<16.2
gen pov_high=0
	replace pov_high=1 if high_ed>=16.2
	
*Hurricane indicators
gen hur_ed_low=hur*ed_low
gen hur_ed_mid=hur*ed_mid
gen hur_ed_hi=hur*ed_high
gen hur_pop_low=hur*pop_low
gen hur_pop_mid=hur*pop_mid
gen hur_pop_hi=hur*pop_high
gen hur_pov_low=hur*pov_low
gen hur_pov_mid=hur*pov_mid
gen hur_pov_hi=hur*pov_high

*Pre-Hurricane indicators
gen pre_hur_ed_low=pre_hur*ed_low
gen pre_hur_ed_mid=pre_hur*ed_mid
gen pre_hur_ed_hi=pre_hur*ed_high
gen pre_hur_pop_low=pre_hur*pop_low
gen pre_hur_pop_mid=pre_hur*pop_mid
gen pre_hur_pop_hi=pre_hur*pop_high
gen pre_hur_pov_low=pre_hur*pov_low
gen pre_hur_pov_mid=pre_hur*pov_mid
gen pre_hur_pov_hi=pre_hur*pov_high

*Post-Hurricane indicators
gen post_hur_ed_low=post_hur*ed_low
gen post_hur_ed_mid=post_hur*ed_mid
gen post_hur_ed_hi=post_hur*ed_high
gen post_hur_pop_low=post_hur*pop_low
gen post_hur_pop_mid=post_hur*pop_mid
gen post_hur_pop_hi=post_hur*pop_high
gen post_hur_pov_low=post_hur*pov_low
gen post_hur_pov_mid=post_hur*pov_mid
gen post_hur_pov_hi=post_hur*pov_high
*
*
*****************************************
*HURRICANE INDICATORS DIF-IN-DIF HETEROGENEITY - DISTANCE TO HIGHWAY
gen hw_dist=hw_nearest_dist/1000 //Convert to kilometers

*Highway variables (less than 0.01 km)
gen hw_close=0.01
	replace hw_close=1 if hw_dist<0.01
gen hw_mid=0
	replace hw_mid=1 if hw_dist>=0.01 & hw_dist<2.5
gen hw_far=0
	replace hw_far=1 if hw_dist>=2.5
	
*Hurricane indicators
gen hur_hw_close=hur*hw_close
gen hur_hw_mid=hur*hw_mid
gen hur_hw_far=hur*hw_far
 
*Pre-Hurricane indicators
gen pre_hur_hw_close=pre_hur*hw_close
gen pre_hur_hw_mid=pre_hur*hw_mid
gen pre_hur_hw_far=pre_hur*hw_far

*Post-Hurricane indicators
gen post_hur_hw_close=post_hur*hw_close
gen post_hur_hw_mid=post_hur*hw_mid
gen post_hur_hw_far=post_hur*hw_far


*****************************************
*DEFINING STATION SAMPLE RESTRICTIONS
gen storm_window=.
	replace storm_window=1 if hur==1|pre_hur==1|post_hur==1
	gsort date -storm_window
	by date: carryforward storm_window, replace
	replace storm_window=0 if missing(storm_window)
gen miss=0 
	replace miss=1 if missing(retail) 
gen tot=1 
	
*Main Sample - 75% reporting over entire period
bys station_id: egen tot_ns=total(tot) if storm_window==0
bys station_id: egen tot_miss_ns=total(miss) if storm_window==0
gen report_ratio=1-tot_miss_ns/tot_ns
	gsort station_id -report_ratio
	by station_id: carryforward report_ratio, replace
	gsort station_id date
gen sample_main=0
	replace sample_main=1 if report_ratio>=0.75
	label var sample_main "Station Sample: 75% station-year reporting"
drop tot_ns tot_miss_ns report_ratio

*Sample 2 - 75% reporting in any year  
bys station_id: egen tot_ns4=total(tot) if storm_window==0 & year==2004
bys station_id: egen tot_ns5=total(tot) if storm_window==0 & year==2005
bys station_id: egen tot_ns6=total(tot) if storm_window==0 & year==2006
bys station_id: egen tot_ns7=total(tot) if storm_window==0 & year==2007
bys station_id: egen tot_ns8=total(tot) if storm_window==0 & year==2008
bys station_id: egen tot_miss_ns4=total(miss) if storm_window==0 & year==2004
bys station_id: egen tot_miss_ns5=total(miss) if storm_window==0 & year==2005
bys station_id: egen tot_miss_ns6=total(miss) if storm_window==0 & year==2006
bys station_id: egen tot_miss_ns7=total(miss) if storm_window==0 & year==2007
bys station_id: egen tot_miss_ns8=total(miss) if storm_window==0 & year==2008
gen report_ratio4=1-tot_miss_ns4/tot_ns4
	gsort station_id -report_ratio4
	by station_id: carryforward report_ratio4, replace
gen report_ratio5=1-tot_miss_ns5/tot_ns5
	gsort station_id -report_ratio5
	by station_id: carryforward report_ratio5, replace
gen report_ratio6=1-tot_miss_ns6/tot_ns6
	gsort station_id -report_ratio6
	by station_id: carryforward report_ratio6, replace
gen report_ratio7=1-tot_miss_ns7/tot_ns7
	gsort station_id -report_ratio7
	by station_id: carryforward report_ratio7, replace
gen report_ratio8=1-tot_miss_ns8/tot_ns8
	gsort station_id -report_ratio8
	by station_id: carryforward report_ratio8, replace
gsort station_id date
gen sample_alt1=0
	replace sample_alt1=1 if report_ratio4>=0.75 & year==2004
	replace sample_alt1=1 if report_ratio5>=0.75 & year==2005
	replace sample_alt1=1 if report_ratio6>=0.75 & year==2006
	replace sample_alt1=1 if report_ratio7>=0.75 & year==2007
	replace sample_alt1=1 if report_ratio8>=0.75 & year==2008
	label var sample_alt1 "Station Sample: 75% reporting each year"
drop tot_ns4-report_ratio8

*Sample 3 - 75% reporting in 2004
bys station_id: egen tot_ns4=total(tot) if storm_window==0 & year==2004
bys station_id: egen tot_miss_ns4=total(miss) if storm_window==0 & year==2004
gen report_ratio4=1-tot_miss_ns4/tot_ns4
	gsort station_id -report_ratio4
	by station_id: carryforward report_ratio4, replace
gen sample_alt2=0
	replace sample_alt2=1 if report_ratio4>=0.75
	label var sample_alt2 "Station Sample: 75% reporting in 2004"
drop tot_ns4-report_ratio4

*SAVING DATA	
save $data_davis\gouging_clean_20191124, replace
